{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Workflow Performance Analysis\n",
    "\n",
    "This notebook analyzes the performance of Nemesis file processing workflows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import warnings\n",
    "from datetime import datetime\n",
    "\n",
    "import matplotlib.pyplot as plt\n",
    "import pandas as pd\n",
    "import seaborn as sns\n",
    "from gql import Client, gql\n",
    "from gql.transport.requests import RequestsHTTPTransport\n",
    "\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "\n",
    "# Set up plotting style\n",
    "plt.style.use(\"seaborn-v0_8\")\n",
    "sns.set_palette(\"husl\")\n",
    "\n",
    "# Set up the GraphQL client\n",
    "hasura_url = os.getenv(\"HASURA_GRAPHQL_URL\", \"http://hasura:8080/v1/graphql\")\n",
    "admin_secret = os.getenv(\"HASURA_ADMIN_SECRET\", \"\")\n",
    "\n",
    "transport = RequestsHTTPTransport(url=hasura_url, headers={\"x-hasura-admin-secret\": admin_secret})\n",
    "\n",
    "client = Client(transport=transport, fetch_schema_from_transport=True)\n",
    "print(f\"Connected to Hasura at: {hasura_url}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Collection\n",
    "\n",
    "Let's collect all the necessary data for our performance analysis."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get all files data with timestamps and sizes\n",
    "files_query = gql(\"\"\"\n",
    "    query {\n",
    "        files_enriched {\n",
    "            object_id\n",
    "            file_name\n",
    "            extension\n",
    "            size\n",
    "            originating_object_id\n",
    "            created_at\n",
    "            updated_at\n",
    "        }\n",
    "    }\n",
    "\"\"\")\n",
    "\n",
    "print(\"Fetching files data...\")\n",
    "files_result = client.execute(files_query)\n",
    "files_df = pd.DataFrame(files_result[\"files_enriched\"])\n",
    "\n",
    "# Convert timestamps to datetime objects\n",
    "files_df[\"created_at\"] = pd.to_datetime(files_df[\"created_at\"])\n",
    "files_df[\"updated_at\"] = pd.to_datetime(files_df[\"updated_at\"])\n",
    "\n",
    "# Add file size in MB for easier analysis\n",
    "files_df[\"size_mb\"] = files_df[\"size\"] / (1024 * 1024)\n",
    "\n",
    "print(f\"Fetched {len(files_df)} files\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get workflow data for processing time analysis\n",
    "workflows_query = gql(\"\"\"\n",
    "    query {\n",
    "        workflows {\n",
    "            wf_id\n",
    "            object_id\n",
    "            filename\n",
    "            status\n",
    "            runtime_seconds\n",
    "            start_time\n",
    "        }\n",
    "    }\n",
    "\"\"\")\n",
    "\n",
    "print(\"Fetching workflows data...\")\n",
    "workflows_result = client.execute(workflows_query)\n",
    "workflows_df = pd.DataFrame(workflows_result[\"workflows\"])\n",
    "\n",
    "if not workflows_df.empty:\n",
    "    # Convert timestamps to datetime objects\n",
    "    workflows_df[\"start_time\"] = pd.to_datetime(workflows_df[\"start_time\"])\n",
    "\n",
    "    # Filter for completed workflows only\n",
    "    completed_workflows = workflows_df[workflows_df[\"status\"].str.lower() == \"completed\"].copy()\n",
    "\n",
    "    print(f\"Fetched {len(workflows_df)} workflows ({len(completed_workflows)} completed)\")\n",
    "else:\n",
    "    print(\"No workflows found\")\n",
    "    completed_workflows = pd.DataFrame()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Basic Statistics\n",
    "\n",
    "Let's calculate the basic statistics for our performance analysis."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calculate basic file statistics\n",
    "total_files = len(files_df)\n",
    "submitted_files = len(files_df[files_df[\"originating_object_id\"].isna()])\n",
    "derived_files = total_files - submitted_files\n",
    "\n",
    "print(\"\\n\" + \"=\" * 80)\n",
    "print(\"                           BASIC STATISTICS\")\n",
    "print(\"=\" * 80)\n",
    "\n",
    "# File counts with nice formatting\n",
    "print(\"\\n📁 FILE COUNTS\")\n",
    "print(f\"   Total processed files:     {total_files:>12,}\")\n",
    "print(f\"   Submitted files:           {submitted_files:>12,}\")\n",
    "print(f\"   Derived files:             {derived_files:>12,}\")\n",
    "if submitted_files > 0:\n",
    "    print(f\"   Derivation ratio:          {derived_files / submitted_files:>12.2f}x\")\n",
    "else:\n",
    "    print(f\"   Derivation ratio:          {'N/A':>12}\")\n",
    "\n",
    "# Calculate file size statistics\n",
    "if not files_df.empty:\n",
    "    # Remove files with zero or null size for size analysis\n",
    "    files_with_size = files_df[files_df[\"size\"] > 0]\n",
    "\n",
    "    print(\"\\n📊 FILE SIZE STATISTICS\")\n",
    "    print(f\"   Files with size data:      {len(files_with_size):>12,}\")\n",
    "    if not files_with_size.empty:\n",
    "        print(f\"   Average file size:         {files_with_size['size_mb'].mean():>12.2f} MB\")\n",
    "        print(f\"   Median file size:          {files_with_size['size_mb'].median():>12.2f} MB\")\n",
    "        print(f\"   Top 10% average size:      {files_with_size['size_mb'].quantile(0.9):>12.2f} MB\")\n",
    "        print(f\"   Largest file:              {files_with_size['size_mb'].max():>12.2f} MB\")\n",
    "        total_gb = files_with_size[\"size_mb\"].sum() / 1024\n",
    "        print(f\"   Total data processed:      {total_gb:>12.2f} GB\")\n",
    "    else:\n",
    "        print(\"   No files with size data available\")\n",
    "\n",
    "# Calculate actual processing time for each file\n",
    "# For files where updated_at == created_at, use workflow runtime_seconds\n",
    "# Otherwise use the difference between updated_at and created_at\n",
    "files_df[\"processing_duration_seconds\"] = (files_df[\"updated_at\"] - files_df[\"created_at\"]).dt.total_seconds()\n",
    "\n",
    "# Merge with workflow data to get runtime_seconds where needed\n",
    "if not completed_workflows.empty:\n",
    "    files_with_workflow = files_df.merge(\n",
    "        completed_workflows[[\"object_id\", \"runtime_seconds\"]], on=\"object_id\", how=\"left\"\n",
    "    )\n",
    "\n",
    "    # Use the maximum of file processing duration or workflow runtime\n",
    "    files_with_workflow[\"actual_processing_time\"] = files_with_workflow[\n",
    "        [\"processing_duration_seconds\", \"runtime_seconds\"]\n",
    "    ].max(axis=1, skipna=True)\n",
    "\n",
    "    # For files without workflow data, use the processing duration\n",
    "    files_with_workflow[\"actual_processing_time\"] = files_with_workflow[\"actual_processing_time\"].fillna(\n",
    "        files_with_workflow[\"processing_duration_seconds\"]\n",
    "    )\n",
    "\n",
    "    # Remove files with zero or negative processing time\n",
    "    files_with_processing_time = files_with_workflow[files_with_workflow[\"actual_processing_time\"] > 0]\n",
    "\n",
    "    print(\"\\n⏱️ PROCESSING TIME STATISTICS\")\n",
    "    print(f\"   Files with processing data: {len(files_with_processing_time):>11,}\")\n",
    "    if not files_with_processing_time.empty:\n",
    "        avg_time = files_with_processing_time[\"actual_processing_time\"].mean()\n",
    "        median_time = files_with_processing_time[\"actual_processing_time\"].median()\n",
    "        p90_time = files_with_processing_time[\"actual_processing_time\"].quantile(0.9)\n",
    "        max_time = files_with_processing_time[\"actual_processing_time\"].max()\n",
    "        total_time = files_with_processing_time[\"actual_processing_time\"].sum()\n",
    "\n",
    "        print(f\"   Average processing time:   {avg_time:>12.2f} seconds\")\n",
    "        print(f\"   Median processing time:    {median_time:>12.2f} seconds\")\n",
    "        print(f\"   Top 10% processing time:   {p90_time:>12.2f} seconds\")\n",
    "        print(f\"   Longest processing time:   {max_time:>12.2f} seconds\")\n",
    "        print(f\"   Total processing time:     {total_time / 60:>12.2f} minutes\")\n",
    "        print(f\"                              {total_time / 3600:>12.2f} hours\")\n",
    "    else:\n",
    "        print(\"   No processing time data available\")\n",
    "else:\n",
    "    print(\"\\n⏱️ PROCESSING TIME STATISTICS\")\n",
    "    print(\"   No workflow data available for processing time analysis\")\n",
    "    # Still calculate from file timestamps\n",
    "    files_with_processing_time = files_df[files_df[\"processing_duration_seconds\"] > 0]\n",
    "    if not files_with_processing_time.empty:\n",
    "        print(f\"   Files with timestamp data:  {len(files_with_processing_time):>11,}\")\n",
    "        avg_time = files_with_processing_time[\"processing_duration_seconds\"].mean()\n",
    "        median_time = files_with_processing_time[\"processing_duration_seconds\"].median()\n",
    "        print(f\"   Average processing time:   {avg_time:>12.2f} seconds (from timestamps)\")\n",
    "        print(f\"   Median processing time:    {median_time:>12.2f} seconds (from timestamps)\")\n",
    "        # Set actual_processing_time for later use\n",
    "        files_df[\"actual_processing_time\"] = files_df[\"processing_duration_seconds\"]\n",
    "    else:\n",
    "        print(\"   No processing time data available\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Total Processing Time and Files Per Minute\n",
    "\n",
    "Calculate the total time from first submission to last completion and files processed per minute."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calculate total processing time from first submitted file to last processed file\n",
    "if not files_df.empty:\n",
    "    # Get submitted files only for the start time\n",
    "    submitted_files_df = files_df[files_df[\"originating_object_id\"].isna()]\n",
    "\n",
    "    if not submitted_files_df.empty:\n",
    "        first_submission = submitted_files_df[\"created_at\"].min()\n",
    "        last_completion = files_df[\"updated_at\"].max()  # Use all files for completion time\n",
    "\n",
    "        total_processing_duration = last_completion - first_submission\n",
    "        total_processing_minutes = total_processing_duration.total_seconds() / 60\n",
    "        total_processing_hours = total_processing_minutes / 60\n",
    "\n",
    "        print(\"\\n\" + \"=\" * 80)\n",
    "        print(\"                    TOTAL PROCESSING TIME ANALYSIS\")\n",
    "        print(\"=\" * 80)\n",
    "        print(\"\\n⏰ TIMELINE ANALYSIS\")\n",
    "        print(f\"   First file submitted:      {first_submission}\")\n",
    "        print(f\"   Last file completed:       {last_completion}\")\n",
    "        print(f\"   Total processing duration: {total_processing_duration}\")\n",
    "        print(f\"   Total processing time:     {total_processing_minutes:>12.2f} minutes\")\n",
    "        print(f\"                              {total_processing_hours:>12.2f} hours\")\n",
    "\n",
    "        # Calculate files per minute\n",
    "        if total_processing_minutes > 0:\n",
    "            submitted_files_per_minute = submitted_files / total_processing_minutes\n",
    "            total_files_per_minute = total_files / total_processing_minutes\n",
    "\n",
    "            print(\"\\n📈 THROUGHPUT ANALYSIS\")\n",
    "            print(f\"   Submitted files per minute: {submitted_files_per_minute:>11.2f}\")\n",
    "            print(f\"   Total files per minute:     {total_files_per_minute:>11.2f}\")\n",
    "            print(f\"   Files per hour:            {total_files_per_minute * 60:>12.2f}\")\n",
    "            print(f\"   Files per day:             {total_files_per_minute * 60 * 24:>12.0f}\")\n",
    "\n",
    "            # Calculate data throughput if we have size data\n",
    "            if \"files_with_size\" in locals() and not files_with_size.empty:\n",
    "                total_data_mb = files_with_size[\"size_mb\"].sum()\n",
    "                data_per_minute = total_data_mb / total_processing_minutes\n",
    "                print(f\"   Data processed per minute: {data_per_minute:>12.2f} MB/min\")\n",
    "                print(f\"   Data processed per hour:   {data_per_minute * 60:>12.2f} MB/hour\")\n",
    "                print(f\"                              {data_per_minute * 60 / 1024:>12.2f} GB/hour\")\n",
    "        else:\n",
    "            print(\"   Cannot calculate throughput - no processing time\")\n",
    "    else:\n",
    "        print(\"No submitted files found for processing time analysis\")\n",
    "else:\n",
    "    print(\"No files found for processing time analysis\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## File Size Analysis and Visualizations\n",
    "\n",
    "Let's create detailed visualizations of file sizes and processing patterns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# File size distribution analysis\n",
    "if not files_df.empty:\n",
    "    files_with_size = files_df[files_df[\"size\"] > 0]\n",
    "\n",
    "    if not files_with_size.empty:\n",
    "        # Create comprehensive file size analysis\n",
    "        fig, axes = plt.subplots(2, 2, figsize=(15, 12))\n",
    "\n",
    "        # 1. File size distribution histogram\n",
    "        axes[0, 0].hist(files_with_size[\"size_mb\"], bins=50, alpha=0.7, edgecolor=\"black\")\n",
    "        axes[0, 0].set_title(\"File Size Distribution\")\n",
    "        axes[0, 0].set_xlabel(\"File Size (MB)\")\n",
    "        axes[0, 0].set_ylabel(\"Frequency\")\n",
    "        axes[0, 0].set_yscale(\"log\")\n",
    "\n",
    "        # 2. File size box plot\n",
    "        axes[0, 1].boxplot(files_with_size[\"size_mb\"], vert=True)\n",
    "        axes[0, 1].set_title(\"File Size Box Plot\")\n",
    "        axes[0, 1].set_ylabel(\"File Size (MB)\")\n",
    "        axes[0, 1].set_yscale(\"log\")\n",
    "\n",
    "        # 3. Top file extensions by count\n",
    "        top_extensions = files_with_size[\"extension\"].value_counts().head(10)\n",
    "        axes[1, 0].bar(range(len(top_extensions)), top_extensions.values)\n",
    "        axes[1, 0].set_title(\"Top 10 File Extensions by Count\")\n",
    "        axes[1, 0].set_xlabel(\"Extension\")\n",
    "        axes[1, 0].set_ylabel(\"Count\")\n",
    "        axes[1, 0].set_xticks(range(len(top_extensions)))\n",
    "        axes[1, 0].set_xticklabels(top_extensions.index, rotation=45)\n",
    "\n",
    "        # 4. File size by extension (top 10)\n",
    "        ext_sizes = files_with_size.groupby(\"extension\")[\"size_mb\"].mean().sort_values(ascending=False).head(10)\n",
    "        axes[1, 1].bar(range(len(ext_sizes)), ext_sizes.values)\n",
    "        axes[1, 1].set_title(\"Average File Size by Extension (Top 10)\")\n",
    "        axes[1, 1].set_xlabel(\"Extension\")\n",
    "        axes[1, 1].set_ylabel(\"Average Size (MB)\")\n",
    "        axes[1, 1].set_xticks(range(len(ext_sizes)))\n",
    "        axes[1, 1].set_xticklabels(ext_sizes.index, rotation=45)\n",
    "\n",
    "        plt.tight_layout()\n",
    "        plt.show()\n",
    "\n",
    "        print(\"\\n\" + \"=\" * 80)\n",
    "        print(\"                        FILE SIZE ANALYSIS RESULTS\")\n",
    "        print(\"=\" * 80)\n",
    "\n",
    "        # Print top 10% file sizes\n",
    "        top_10_percent = files_with_size.nlargest(int(len(files_with_size) * 0.1), \"size_mb\")\n",
    "        print(\"\\n📊 TOP 10% LARGEST FILES ANALYSIS\")\n",
    "        print(f\"   Number of files in top 10%: {len(top_10_percent):>11,}\")\n",
    "        print(f\"   Average size of top 10%:    {top_10_percent['size_mb'].mean():>12.2f} MB\")\n",
    "        print(\n",
    "            f\"   Size range:                 {top_10_percent['size_mb'].min():>12.2f} - {top_10_percent['size_mb'].max():.2f} MB\"\n",
    "        )\n",
    "\n",
    "        # Show top 10 largest files\n",
    "        print(\"\\n🏆 TOP 10 LARGEST FILES\")\n",
    "        largest_files = files_with_size.nlargest(10, \"size_mb\")[[\"file_name\", \"extension\", \"size_mb\"]]\n",
    "        for i, (_idx, row) in enumerate(largest_files.iterrows(), 1):\n",
    "            print(f\"   {i:>2}. {row['file_name']} ({row['extension']}): {row['size_mb']:.2f} MB\")\n",
    "\n",
    "        print(\"=\" * 80)\n",
    "    else:\n",
    "        print(\"No files with size data found\")\n",
    "else:\n",
    "    print(\"No files found for size analysis\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Processing Time Analysis and Visualizations\n",
    "\n",
    "Analyze workflow processing times and create visualizations."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Processing time analysis\n",
    "if not completed_workflows.empty:\n",
    "    # Create comprehensive processing time analysis\n",
    "    fig, axes = plt.subplots(1, 2, figsize=(15, 6))  # Changed from (2, 2) to (1, 2)\n",
    "\n",
    "    # 1. Processing time distribution histogram\n",
    "    axes[0].hist(completed_workflows[\"runtime_seconds\"], bins=50, alpha=0.7, edgecolor=\"black\")\n",
    "    axes[0].set_title(\"Processing Time Distribution\")\n",
    "    axes[0].set_xlabel(\"Processing Time (seconds)\")\n",
    "    axes[0].set_ylabel(\"Frequency\")\n",
    "    axes[0].set_yscale(\"log\")\n",
    "\n",
    "    # 2. Processing time percentiles\n",
    "    percentiles = [50, 75, 90, 95, 99]\n",
    "    percentile_values = [completed_workflows[\"runtime_seconds\"].quantile(p / 100) for p in percentiles]\n",
    "    axes[1].bar(range(len(percentiles)), percentile_values)\n",
    "    axes[1].set_title(\"Processing Time Percentiles\")\n",
    "    axes[1].set_xlabel(\"Percentile\")\n",
    "    axes[1].set_ylabel(\"Processing Time (seconds)\")\n",
    "    axes[1].set_xticks(range(len(percentiles)))\n",
    "    axes[1].set_xticklabels([f\"P{p}\" for p in percentiles])\n",
    "\n",
    "    plt.tight_layout()\n",
    "    plt.show()\n",
    "\n",
    "    print(\"\\n\" + \"=\" * 80)\n",
    "    print(\"                    PROCESSING TIME ANALYSIS RESULTS\")\n",
    "    print(\"=\" * 80)\n",
    "\n",
    "    # Print top 10% processing times\n",
    "    top_10_percent_time = completed_workflows.nlargest(int(len(completed_workflows) * 0.1), \"runtime_seconds\")\n",
    "    print(\"\\n⏱️ TOP 10% LONGEST PROCESSING TIMES\")\n",
    "    print(f\"   Number of workflows in top 10%: {len(top_10_percent_time):>12,}\")\n",
    "    print(f\"   Average processing time:        {top_10_percent_time['runtime_seconds'].mean():>12.2f} seconds\")\n",
    "    print(\n",
    "        f\"   Time range:                     {top_10_percent_time['runtime_seconds'].min():>12.2f} - {top_10_percent_time['runtime_seconds'].max():.2f} seconds\"\n",
    "    )\n",
    "\n",
    "    # Show top 10 longest processing times\n",
    "    print(\"\\n🕐 TOP 10 LONGEST PROCESSING TIMES\")\n",
    "    longest_times = completed_workflows.nlargest(10, \"runtime_seconds\")[[\"filename\", \"runtime_seconds\", \"start_time\"]]\n",
    "    for i, (_idx, row) in enumerate(longest_times.iterrows(), 1):\n",
    "        print(f\"   {i:>2}. {row['filename']}: {row['runtime_seconds']:.2f} seconds (started: {row['start_time']})\")\n",
    "\n",
    "    print(\"=\" * 80)\n",
    "else:\n",
    "    print(\"No completed workflows found for processing time analysis\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Performance Summary Report\n",
    "\n",
    "Generate a comprehensive performance summary report."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Generate comprehensive performance report\n",
    "print(\"\\n\" + \"=\" * 80)\n",
    "print(\"                 NEMESIS WORKFLOW PERFORMANCE REPORT\")\n",
    "print(\"=\" * 80)\n",
    "\n",
    "# Overall statistics\n",
    "print(\"\\n📊 OVERALL STATISTICS\")\n",
    "print(\"-\" * 40)\n",
    "print(f\"Total files processed:     {total_files:>12,}\")\n",
    "print(f\"Submitted files:           {submitted_files:>12,}\")\n",
    "print(f\"Derived files:             {derived_files:>12,}\")\n",
    "if submitted_files > 0:\n",
    "    print(f\"Derivation ratio:          {derived_files / submitted_files:>12.2f}x\")\n",
    "else:\n",
    "    print(f\"Derivation ratio:          {'N/A':>12}\")\n",
    "\n",
    "# Processing time summary\n",
    "if not files_df.empty and \"submitted_files_df\" in locals() and not submitted_files_df.empty:\n",
    "    print(\"\\n⏱️ PROCESSING TIME SUMMARY\")\n",
    "    print(\"-\" * 40)\n",
    "    print(f\"Total processing time:     {total_processing_hours:>12.2f} hours\")\n",
    "    if total_processing_minutes > 0:\n",
    "        print(f\"Files per minute:          {total_files_per_minute:>12.2f}\")\n",
    "        print(f\"Files per hour:            {total_files_per_minute * 60:>12.2f}\")\n",
    "        print(f\"Processing rate:           {total_files / total_processing_hours:>12.2f} files/hour\")\n",
    "\n",
    "# File size summary\n",
    "if not files_df.empty and \"files_with_size\" in locals() and not files_with_size.empty:\n",
    "    print(\"\\n📁 FILE SIZE SUMMARY\")\n",
    "    print(\"-\" * 40)\n",
    "    print(f\"Average file size:         {files_with_size['size_mb'].mean():>12.2f} MB\")\n",
    "    print(f\"Median file size:          {files_with_size['size_mb'].median():>12.2f} MB\")\n",
    "    print(f\"Top 10% average size:      {files_with_size['size_mb'].quantile(0.9):>12.2f} MB\")\n",
    "    print(f\"Largest file:              {files_with_size['size_mb'].max():>12.2f} MB\")\n",
    "    print(f\"Total data processed:      {files_with_size['size_mb'].sum() / 1024:>12.2f} GB\")\n",
    "\n",
    "# Workflow performance summary (original workflow data)\n",
    "if not completed_workflows.empty:\n",
    "    print(\"\\n🏭 WORKFLOW ENGINE SUMMARY\")\n",
    "    print(\"-\" * 40)\n",
    "    print(f\"Completed workflows:       {len(completed_workflows):>12,}\")\n",
    "    print(f\"Average workflow time:     {completed_workflows['runtime_seconds'].mean():>12.2f} seconds\")\n",
    "    print(f\"Median workflow time:      {completed_workflows['runtime_seconds'].median():>12.2f} seconds\")\n",
    "    print(f\"Top 10% workflow time:     {completed_workflows['runtime_seconds'].quantile(0.9):>12.2f} seconds\")\n",
    "    print(f\"Longest workflow time:     {completed_workflows['runtime_seconds'].max():>12.2f} seconds\")\n",
    "    print(f\"Workflow coverage:         {len(completed_workflows) / total_files * 100:>12.1f}% of files\")\n",
    "\n",
    "# Performance insights\n",
    "print(\"\\n💡 PERFORMANCE INSIGHTS\")\n",
    "print(\"-\" * 40)\n",
    "\n",
    "if submitted_files > 0:\n",
    "    expansion_ratio = derived_files / submitted_files\n",
    "    print(f\"• Container expansion: Each submitted file generates {expansion_ratio:.2f} derived files on average\")\n",
    "    if expansion_ratio > 3:\n",
    "        print(\"  → High expansion ratio indicates significant archive/container processing\")\n",
    "    elif expansion_ratio > 1.5:\n",
    "        print(\"  → Moderate expansion from container extraction\")\n",
    "    else:\n",
    "        print(\"  → Low expansion suggests mostly individual files\")\n",
    "\n",
    "if not files_df.empty and \"total_processing_minutes\" in locals() and total_processing_minutes > 0:\n",
    "    print(f\"• Processing throughput: {total_files_per_minute:.2f} files/minute overall\")\n",
    "    if \"files_with_size\" in locals() and not files_with_size.empty:\n",
    "        data_throughput = files_with_size[\"size_mb\"].sum() / total_processing_minutes\n",
    "        print(f\"• Data throughput: {data_throughput:.2f} MB/minute ({data_throughput * 60 / 1024:.2f} GB/hour)\")\n",
    "\n",
    "# Correlation analysis if we have both size and processing time data\n",
    "if (\n",
    "    \"completed_workflows\" in locals()\n",
    "    and not completed_workflows.empty\n",
    "    and \"files_with_size\" in locals()\n",
    "    and not files_with_size.empty\n",
    "):\n",
    "    merged_data = pd.merge(completed_workflows, files_with_size, on=\"object_id\", how=\"inner\")\n",
    "    if not merged_data.empty and len(merged_data) > 10:  # Need enough data points\n",
    "        correlation = merged_data[\"size_mb\"].corr(merged_data[\"runtime_seconds\"])\n",
    "        print(f\"• File size vs processing time correlation: {correlation:.3f}\")\n",
    "        if correlation > 0.5:\n",
    "            print(\"  → Strong positive correlation: larger files take significantly longer\")\n",
    "        elif correlation > 0.3:\n",
    "            print(\"  → Moderate positive correlation: larger files tend to take longer\")\n",
    "        elif correlation < -0.3:\n",
    "            print(\"  → Negative correlation: larger files process faster (investigate)\")\n",
    "        else:\n",
    "            print(\"  → Weak correlation: processing time not strongly size-dependent\")\n",
    "\n",
    "# Processing efficiency insights\n",
    "if (\n",
    "    \"files_with_processing_time\" in locals()\n",
    "    and not files_with_processing_time.empty\n",
    "    and \"total_processing_minutes\" in locals()\n",
    "    and total_processing_minutes > 0\n",
    "):\n",
    "    # Calculate actual CPU time vs wall clock time ratio\n",
    "    if hasattr(files_with_processing_time, \"actual_processing_time\"):\n",
    "        total_cpu_time_minutes = files_with_processing_time[\"actual_processing_time\"].sum() / 60\n",
    "        efficiency_ratio = total_cpu_time_minutes / total_processing_minutes\n",
    "        print(f\"• Processing efficiency: {efficiency_ratio:.2f} (CPU time / wall time)\")\n",
    "        if efficiency_ratio > 0.8:\n",
    "            print(\"  → High efficiency: CPU-bound processing with good parallelization\")\n",
    "        elif efficiency_ratio > 0.4:\n",
    "            print(\"  → Moderate efficiency: some parallel processing occurring\")\n",
    "        else:\n",
    "            print(\"  → Low efficiency: significant I/O wait or limited parallelization\")\n",
    "\n",
    "print(\"\\n\" + \"=\" * 80)\n",
    "print(f\"Report generated at: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}\")\n",
    "print(\"=\" * 80)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Export Results\n",
    "\n",
    "Export the performance data for further analysis or reporting."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Export performance data to CSV files\n",
    "print(\"\\n\" + \"=\" * 60)\n",
    "print(\"                    EXPORTING RESULTS\")\n",
    "print(\"=\" * 60)\n",
    "\n",
    "if not files_df.empty:\n",
    "    # Export enhanced files data with processing times\n",
    "    files_summary = files_df[\n",
    "        [\"object_id\", \"file_name\", \"extension\", \"size_mb\", \"originating_object_id\", \"created_at\", \"updated_at\"]\n",
    "    ].copy()\n",
    "\n",
    "    # Add processing duration from timestamps\n",
    "    files_summary[\"processing_duration_seconds\"] = (files_df[\"updated_at\"] - files_df[\"created_at\"]).dt.total_seconds()\n",
    "\n",
    "    # Add actual processing time if we calculated it\n",
    "    if \"files_with_processing_time\" in locals() and hasattr(files_with_processing_time, \"actual_processing_time\"):\n",
    "        # Merge the actual processing time back to the summary\n",
    "        processing_time_data = files_with_processing_time[[\"object_id\", \"actual_processing_time\"]].copy()\n",
    "        files_summary = files_summary.merge(processing_time_data, on=\"object_id\", how=\"left\")\n",
    "\n",
    "    files_summary.to_csv(\"nemesis_files_performance.csv\", index=False)\n",
    "    print(\"✓ Files performance data exported to: nemesis_files_performance.csv\")\n",
    "\n",
    "if not completed_workflows.empty:\n",
    "    # Export workflow data\n",
    "    workflow_summary = completed_workflows[[\"wf_id\", \"object_id\", \"filename\", \"runtime_seconds\", \"start_time\"]].copy()\n",
    "    workflow_summary.to_csv(\"nemesis_workflow_performance.csv\", index=False)\n",
    "    print(\"✓ Workflow performance data exported to: nemesis_workflow_performance.csv\")\n",
    "\n",
    "# Create an enhanced summary statistics file\n",
    "summary_stats = {\n",
    "    \"metric\": [\n",
    "        \"total_files\",\n",
    "        \"submitted_files\",\n",
    "        \"derived_files\",\n",
    "        \"derivation_ratio\",\n",
    "        \"avg_file_size_mb\",\n",
    "        \"median_file_size_mb\",\n",
    "        \"top_10_percent_file_size_mb\",\n",
    "        \"total_data_gb\",\n",
    "        \"avg_individual_processing_time_sec\",\n",
    "        \"median_individual_processing_time_sec\",\n",
    "        \"top_10_percent_processing_time_sec\",\n",
    "        \"avg_workflow_time_sec\",\n",
    "        \"median_workflow_time_sec\",\n",
    "        \"total_processing_hours\",\n",
    "        \"files_per_minute\",\n",
    "        \"files_per_hour\",\n",
    "        \"data_throughput_mb_per_minute\",\n",
    "    ],\n",
    "    \"value\": [\n",
    "        total_files,\n",
    "        submitted_files,\n",
    "        derived_files,\n",
    "        derived_files / submitted_files if submitted_files > 0 else 0,\n",
    "        files_with_size[\"size_mb\"].mean() if \"files_with_size\" in locals() and not files_with_size.empty else 0,\n",
    "        files_with_size[\"size_mb\"].median() if \"files_with_size\" in locals() and not files_with_size.empty else 0,\n",
    "        files_with_size[\"size_mb\"].quantile(0.9) if \"files_with_size\" in locals() and not files_with_size.empty else 0,\n",
    "        files_with_size[\"size_mb\"].sum() / 1024 if \"files_with_size\" in locals() and not files_with_size.empty else 0,\n",
    "        files_with_processing_time[\"actual_processing_time\"].mean()\n",
    "        if \"files_with_processing_time\" in locals()\n",
    "        and not files_with_processing_time.empty\n",
    "        and hasattr(files_with_processing_time, \"actual_processing_time\")\n",
    "        else 0,\n",
    "        files_with_processing_time[\"actual_processing_time\"].median()\n",
    "        if \"files_with_processing_time\" in locals()\n",
    "        and not files_with_processing_time.empty\n",
    "        and hasattr(files_with_processing_time, \"actual_processing_time\")\n",
    "        else 0,\n",
    "        files_with_processing_time[\"actual_processing_time\"].quantile(0.9)\n",
    "        if \"files_with_processing_time\" in locals()\n",
    "        and not files_with_processing_time.empty\n",
    "        and hasattr(files_with_processing_time, \"actual_processing_time\")\n",
    "        else 0,\n",
    "        completed_workflows[\"runtime_seconds\"].mean() if not completed_workflows.empty else 0,\n",
    "        completed_workflows[\"runtime_seconds\"].median() if not completed_workflows.empty else 0,\n",
    "        total_processing_hours if \"total_processing_hours\" in locals() else 0,\n",
    "        total_files_per_minute if \"total_files_per_minute\" in locals() else 0,\n",
    "        total_files_per_minute * 60 if \"total_files_per_minute\" in locals() else 0,\n",
    "        (files_with_size[\"size_mb\"].sum() / total_processing_minutes)\n",
    "        if (\n",
    "            \"files_with_size\" in locals()\n",
    "            and not files_with_size.empty\n",
    "            and \"total_processing_minutes\" in locals()\n",
    "            and total_processing_minutes > 0\n",
    "        )\n",
    "        else 0,\n",
    "    ],\n",
    "}\n",
    "\n",
    "summary_df = pd.DataFrame(summary_stats)\n",
    "summary_df.to_csv(\"nemesis_performance_summary.csv\", index=False)\n",
    "print(\"✓ Performance summary exported to: nemesis_performance_summary.csv\")\n",
    "\n",
    "# Export processing time comparison if we have both datasets\n",
    "if (\n",
    "    \"files_with_processing_time\" in locals()\n",
    "    and not files_with_processing_time.empty\n",
    "    and not completed_workflows.empty\n",
    "    and hasattr(files_with_processing_time, \"actual_processing_time\")\n",
    "):\n",
    "    # Create comparison dataset\n",
    "    comparison_data = files_with_processing_time[\n",
    "        [\"object_id\", \"file_name\", \"size_mb\", \"processing_duration_seconds\", \"actual_processing_time\"]\n",
    "    ].copy()\n",
    "\n",
    "    # Merge with workflow data\n",
    "    comparison_data = comparison_data.merge(\n",
    "        completed_workflows[[\"object_id\", \"runtime_seconds\", \"filename\"]], on=\"object_id\", how=\"left\"\n",
    "    )\n",
    "\n",
    "    comparison_data.to_csv(\"nemesis_processing_time_comparison.csv\", index=False)\n",
    "    print(\"✓ Processing time comparison exported to: nemesis_processing_time_comparison.csv\")\n",
    "\n",
    "print(\"\\n📊 EXPORT SUMMARY\")\n",
    "print(f\"   Files exported:            {len(files_df) if not files_df.empty else 0:>12,}\")\n",
    "print(f\"   Workflows exported:        {len(completed_workflows) if not completed_workflows.empty else 0:>12,}\")\n",
    "print(f\"   Summary metrics:           {len(summary_df):>12,}\")\n",
    "\n",
    "print(\"\\n📄 All performance data has been exported for further analysis!\")\n",
    "print(\"=\" * 60)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
